package DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;

import model.ExpiredStudy;

public class ExpiredStudyDao {

	public ExpiredStudyDao() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	String url = "jdbc:mysql://localhost:3306/QUESTUDY";
	String user = "root";
	String pass = "webclass";

	//String pass = "webclass";

	// 마감된 스터디 목록 불러오기
	public ArrayList<ExpiredStudy> getAllExpiredStudyList() throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		ArrayList<ExpiredStudy> exStudyList = new ArrayList<ExpiredStudy>();
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select exStudyId, sid, currentTime, expiredTime from expiredStudy");
			rs = pstmt.executeQuery();
			while (rs.next()) {

				int exStudyId = rs.getInt("exStudyId");
				int sid = rs.getInt("sid");
				Timestamp currentTime = rs.getTimestamp("currentTime");
				Timestamp expiredTime = rs.getTimestamp("expiredTime");
				exStudyList.add(new ExpiredStudy(exStudyId, sid, currentTime,
						expiredTime));
			}

			return exStudyList;

		} catch (Exception ex) {
			ex.printStackTrace();
			return exStudyList;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 마감된 스터디 찾기
	public boolean getExpiredStudy(int sid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select exStudyId, sid, currentTime, expiredTime from expiredStudy where sid=?");
			pstmt.setInt(1, sid);
			rs = pstmt.executeQuery();
			if (rs.next()) {

				return true;
			} else {
				return false;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			return false;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 완료한 스터디 갯수
	public int getExpiredStudyNumber(int uid) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		int expStudyNum = 0;
		try {

			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("select exStudyId from expiredStudy,study where studyId=sid and uid=?");
			pstmt.setInt(1, uid);
			rs = pstmt.executeQuery();
			while (rs.next()) {

				expStudyNum = expStudyNum + 1;
			}

			return expStudyNum;

		} catch (Exception ex) {
			ex.printStackTrace();
			return expStudyNum;
		} finally {
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException ex) {
				}
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}

	// 마감된 스터디 넣기
	public void insertExpiredStudy(ExpiredStudy study) throws Exception {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = DriverManager.getConnection(url, user, pass);
			pstmt = con
					.prepareStatement("insert into expiredStudy(sid, currentTime, expiredTime) values(?,sysdate(),?)");
			pstmt.setInt(1, study.getSid());
			pstmt.setTimestamp(2, study.getExpiredTime());
			pstmt.executeUpdate();

		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (pstmt != null)
				try {
					pstmt.close();
				} catch (SQLException ex) {
				}
			if (con != null)
				try {
					con.close();
				} catch (SQLException ex) {
				}
		}

	}
}